# -*- coding: utf-8 -*-
from sqlalchemy.orm import Session

from app.db.models import TPost, TSource
from app.db.models import TJudge
from datetime import datetime,time,timedelta
from sqlalchemy.sql import text

def get_all_posts_with_judges_sql(db: Session):
    """
    使用原生 SQL 查询所有 TPost 并关联 TJudge
    """
    query = text("""
        SELECT 
            p.id AS post_id,
            p.title AS post_title,
            p.content AS post_content,
            j.id AS judge_id,
            j.category AS judge_category,
            j.anxiety_spread_score AS judge_anxiety_spread_score
        FROM 
            t_post p
        LEFT JOIN 
            t_judge j ON p.id = j.post_id;
    """)
    result = db.execute(query)
    return result.fetchall()
def get_posts_count_with_title(db: Session, title: str):
    today_start = datetime.combine(datetime.today() - timedelta(days=3), time.min)
    result = db.query(TPost).filter(TPost.title == title).filter(TPost.create_date >= today_start).count()
    return result
def get_posts_with_source_sql(db: Session, date_range: datetime):
    """
    使用原生 SQL 查询所有 TPost 并关联 TJudge
    """
    if date_range:
        today_start = date_range
    else:
        today_start = datetime.combine(datetime.today() - timedelta(days=3), time.min)
    query = text("""
        SELECT 
            p.id AS post_id,
            p.title,
            p.content,
            p.url,
            j.id AS source_id,
            j.name AS source_name,
            j.username AS source_username,
            j.is_user_define
        FROM 
            t_post p
        LEFT JOIN 
            t_source j ON p.source_id = j.id
        WHERE p.is_judge != 1 and p.create_date > :today_start;
    """)
    result = db.execute(query, {"today_start": today_start})
    return result.fetchall()


def get_not_ttl_posts_with_source_sql(db: Session, date_range: datetime):
    """
    使用原生 SQL 查询所有 TPost 并关联 TJudge
    """
    if date_range:
        today_start = date_range
    else:
        today_start = datetime.combine(datetime.today(), time.min)
    query = text("""
        SELECT 
            p.id AS post_id,
            p.title,
            p.short_content,
            p.url,
            j.id AS source_id,
            j.name AS source_name,
            j.username AS source_username,
            j.is_user_define
        FROM 
            t_post p
        LEFT JOIN 
            t_source j ON p.source_id = j.id
        WHERE p.ttl is null and  p.short_content is not null and p.create_date > :today_start
        order by p.create_date desc;
    """)
    result = db.execute(query, {"today_start": today_start})
    return result.fetchall()

def create_tpost(db: Session, name: str, title: str, url: str, content: str,content_date: str,short_content: str,
                 is_judge: bool = False, is_user_define: bool = False, source_id: int = None):
    """
    创建一个新的 TPost 记录。

    :param db: SQLAlchemy Session 对象
    :param name: 名称
    :param title: 标题
    :param url: 链接地址
    :param content: 内容
    :param short_content: 内容总结
    :param is_judge: 是否经过评价，默认为 False
    :param is_user_define: 是否用户定制，默认为 False
    :param source_id: 源id，默认为 None
    :return: 创建的 TPost 对象
    """
    # 创建新的 TPost 实例
    new_post = TPost(
        name=name,
        title=title,
        url=url,
        content=content,
        short_content=short_content,
        is_judge=is_judge,
        is_user_define=is_user_define,
        source_id=source_id,
        delete_flag=0,  # 默认数据状态为可用
        content_date = content_date,
        create_by="system",  # 默认创建人为 system
        create_date=datetime.now(),  # 默认创建时间为当前时间
        update_by="system",  # 默认更新人为 system
        update_date=datetime.now()  # 默认更新时间为当前时间
    )

    # 添加到数据库会话
    db.add(new_post)
    db.commit()  # 提交事务
    db.refresh(new_post)  # 刷新实例以获取自动生成的值（如 id）
    return new_post


def update_tpost_is_judge(db: Session, post_id: int, is_judge: bool, update_by: str = "system"):
    """
    根据 id 修改 TPost 记录的 is_judge 字段。

    :param db: SQLAlchemy Session 对象
    :param post_id: 要更新的 TPost 记录的 id
    :param is_judge: 新的 is_judge 值
    :param update_by: 更新人，默认为 "system"
    :return: 更新后的 TPost 对象，如果记录不存在则返回 None
    """
    # 查询要更新的 TPost 记录
    post = db.query(TPost).filter(TPost.id == post_id, TPost.delete_flag == 0).first()

    # 如果记录存在，则更新 is_judge 字段
    if post:
        post.is_judge = is_judge
        post.update_by = update_by
        post.update_date = datetime.now()  # 更新时间为当前时间
        db.commit()  # 提交事务
        db.refresh(post)  # 刷新实例以获取最新的数据
        return post
    else:
        return None  # 如果记录不存在，返回 None
def create_tjudge(db: Session, post_id: int, anxiety_spread_score: int,
                  knowledge_density_score: int, optimism_score: int, quality_score: int,
                  category: str):
    """
    创建一条新的 t_judge 记录

    :param db: SQLAlchemy 数据库会话
    :param create_by: 创建人
    :param post_id: 内容id
    :param anxiety_spread_score: 散布焦虑评分
    :param knowledge_density_score: 知识密集程度评分
    :param optimism_score: 乐观程度评分
    :param quality_score: 质量评分
    :param category: 类别
    :param update_by: 更新人（可选，默认为 None）
    :param delete_flag: 数据状态（可选，默认为 0）
    :return: 创建的 TJudge 对象
    """
    # 创建 TJudge 对象
    new_judge = TJudge(
        delete_flag=0,  # 默认数据状态为可用
        create_by="system",  # 默认创建人为 system
        create_date=datetime.now(),  # 默认创建时间为当前时间
        update_by="system",  # 默认更新人为 system
        update_date=datetime.now(),  # 默认更新时间为当前时间
        post_id=post_id,
        anxiety_spread_score=anxiety_spread_score,
        knowledge_density_score=knowledge_density_score,
        optimism_score=optimism_score,
        quality_score=quality_score,
        category=category
    )

    # 添加到数据库会话
    db.add(new_judge)
    db.commit()
    db.refresh(new_judge)

    return new_judge


# def get_tposts(db: Session):
#     return db.query(models.TPost).all()
#
# def get_tpost_by_id(db: Session, post_id: int):
#     return db.query(models.TPost).filter(models.TPost.id == post_id).first()
#
# def update_tpost(db: Session, post_id: int, name: str = None, num: int = None):
#     tpost = db.query(models.TPost).filter(models.TPost.id == post_id).first()
#     if tpost:
#         if name is not None:
#             tpost.name = name
#         if num is not None:
#             tpost.num = num
#         db.commit()
#         db.refresh(tpost)
#     return tpost
#
# def delete_tpost(db: Session, post_id: int):
#     tpost = db.query(models.TPost).filter(models.TPost.id == post_id).first()
#     if tpost:
#         db.delete(tpost)
#         db.commit()

def create_source(db: Session, name: str, url: str, username: str, is_user_define: bool, create_by: str):
    """
    创建一条新的 TSource 记录
    """
    new_source = TSource(
        name=name,
        url=url,
        username=username,
        is_user_define=is_user_define,
        create_by=create_by,
        create_date=datetime.now(),
        update_by=create_by,
        update_date=datetime.now(),
        delete_flag=0  # 默认状态为可用
    )
    db.add(new_source)
    db.commit()
    db.refresh(new_source)
    return new_source

def get_all_sources(db: Session):
    """
    查询所有 TSource 记录
    """
    return db.query(TSource).filter(TSource.delete_flag == 0).all()

def get_source_by_id(db: Session, source_id: int):
    """
    根据 ID 查询单条 TSource 记录
    """
    return db.query(TSource).filter(TSource.id == source_id, TSource.delete_flag == 0).first()


def update_source(db: Session, source_id: int, name: str = None, url: str = None, username: str = None,
                  is_user_define: bool = None, update_by: str = None):
    """
    更新 TSource 记录
    """
    source = db.query(TSource).filter(TSource.id == source_id, TSource.delete_flag == 0).first()
    if source:
        if name is not None:
            source.name = name
        if url is not None:
            source.url = url
        if username is not None:
            source.username = username
        if is_user_define is not None:
            source.is_user_define = is_user_define
        if update_by is not None:
            source.update_by = update_by
        source.update_date = datetime.now()
        db.commit()
        db.refresh(source)
    return source

def delete_source_soft(db: Session, source_id: int, update_by: str):
    """
    软删除 TSource 记录（更新 delete_flag 为 -1）
    """
    source = db.query(TSource).filter(TSource.id == source_id, TSource.delete_flag == 0).first()
    if source:
        source.delete_flag = -1
        source.update_by = update_by
        source.update_date = datetime.now()
        db.commit()
        db.refresh(source)
    return source

def delete_source_hard(db: Session, source_id: int):
    """
    硬删除 TSource 记录（物理删除）
    """
    source = db.query(TSource).filter(TSource.id == source_id, TSource.delete_flag == 0).first()
    if source:
        db.delete(source)
        db.commit()
    return source
